#ifndef _DATABASE_H_
#define _DATABASE_H_
#include "ros/ros.h"
#include "rads_base.hpp"
#include "std_msgs/String.h"
#include <sstream> // 用于字符串流
#include <vector>  // 用于整数数组
#include <iostream>
#include <sqlite3.h>
#include <ctime>
#include <string>
#include <iomanip>
#include <cstdlib>
#include <sys/wait.h>
#include "ros/time.h" // 用于ros::Time
#include <tf/transform_listener.h>
#include <thread>
#include <geometry_msgs/Vector3.h>
#include <geometry_msgs/PoseWithCovarianceStamped.h>
#include <nav_msgs/Path.h>
#include <fstream>
#include <sys/types.h>
#include <sys/ipc.h>
#include <sys/shm.h>
#include <cstdio>
#include <unistd.h>
#include <math.h>
#include "rads/rads_scmdret.h"
#include "rads/rads_cmd.h"
#include "rads/rads_scmd.h"
#include "rads/rads_acks.h"
#include "rads/rads_version.h"
#include "rads/rads_versions.h"
#include "rads/rads_driving.h"
#include "rads/rads_trackext.h"
#include <chrono>
#include <mutex>
#include <cmath>
#include <stdexcept>
#include <functional>
#include <std_msgs/Float32.h>

using namespace std;

extern std::mutex g_db_mutex;
/**定义数据结构体start */
typedef struct
{
    double x;
    double y;
    double z;
    double lidar_heading;
    double ndt_matching;
    double rtk_x;
    double rtk_y;
    double rtk_z;
    double rtk_heading;
    double lng;
    double lat;
    int position_flag;
    int orientation_flag;
    int num_point;
    int stop_flag;
    int stop_time;
    int line_id;
    int map_id;
} car;

typedef struct
{
    int model;
    int steering;
    int acc;
    int brake;
    int gear;
    int target_speed;
    int rpml;
    int rpmr;
    int lignt;
    int audio;
    int sign;
    int camera_hor;
    int camera_ver;
    int unknow_1;
    int unknow_2;
    int unknow_3;
    int unknow_4;
    int unknow_5;
    int map_id;
    int line_id;
    int num_point;
    int alltotal;
    string ctime;
} car_driving_log;

typedef struct
{
    double speed;
    double x;
    double y;
    double z;
    double lidar_heading;
    double ndt_matching;
    double rtk_x;
    double rtk_y;
    double rtk_z;
    double rtk_heading;
    double lng;
    double lat;
    int position_flag;
    int orientation_flag;
    string ctime;
} car_log;

typedef struct
{

    double x;
    double y;
    double z;
    double ox;
    double oy;
    double oz;
    double ow;
    int map_id;
    double xx;
    double yy;
    double head;
} fist_pose;

typedef struct
{
    int num_point;
    double x;
    double y;
    double z;
    double lidar_heading;
    double rtk_x;
    double rtk_y;
    double rtk_z;
    double rtk_heading;
    double lng;
    double lat;
    int position_flag;
    int orientation_flag;
} go_back;

typedef struct
{
    int num;
    int map_id;
    double x;
    double y;
    double z;
    double lidar_heading;
    double ndt_matching;
    double rtk_x;
    double rtk_y;
    double rtk_z;
    double lng;
    double lat;
    double rtk_heading;
    int position_flag;
    int orientation_flag;
} point_all;

typedef struct
{
    double x;
    double y;
    double z;
    double lidar_heading;
    double ndt_matching;
    double rtk_x;
    double rtk_y;
    double rtk_z;
    double lng;
    double lat;
    double rtk_heading;
    int position_flag;
    int orientation_flag;
    int num_point;
    int alltotal;
} point_data;

typedef struct
{
    int num;
    int map_id;
    double x;
    double y;
    double z;
    double ox;
    double oy;
    double oz;
    double ow;
    double lidar_heading;
    double ndt_matching;
    double rtk_x;
    double rtk_y;
    double rtk_z;
    double lng;
    double lat;
    double rtk_heading;
    int position_flag;
    int orientation_flag;
} start_machine;

typedef struct
{
    int type;
    string cmd;
    string ctime;
} tcp_cmd;

typedef struct
{
    int type;
    string publish_date;
    string publish_type;
    string package_size;
    string is_must;
    string node_name;
    string process_name;
    string major_minor_patch;
    string md5_code;
    string temp_down_path;
    string description;
    string ext;
    string ext2;
} version;

typedef struct
{
    int model;
    int soc;
    int err;
    int rpm_l;
    int rpm_r;
    int key_1;
    int key_2;
    int key_3;
    int key_4;
    int key_5;
    int key_6;
    int key_7;
    int key_8;
    int ladar_1;
    int ladar_2;
    int ladar_3;
    int ladar_4;
    int ladar_5;
    int ladar_6;
    int ladar_7;
    int ladar_8;
    int unknow_1;
    int unknow_2;
    int unknow_3;
    int unknow_4;
    int unknow_5;
    int unknow_6;
    int heartbeat;
    string ctime;
} pcb_log;
/**定义数据结构体end */
static car_log latest_car_log_data;
static pcb_log latest_pcb_log_data;
static bool car_log_updated = false;
static bool pcb_log_updated = false;
/**任意点导航 start*/
struct ListNode
{
    int value;
    ListNode *next;
    ListNode(int x) : value(x), next(nullptr) {}
};
struct AxisData
{
    double x;
    double y;
    AxisData() {};
    AxisData(double x, double y) : x(x), y(y) {}
};
ListNode *lists[180];
vector<AxisData> lidar_points;
vector<double> Lidar_points_x;
vector<double> Lidar_points_y;
vector<double> Nav_x;
vector<double> Nav_y;
int Path_test[1000];
int Current_NUM;
/**任意点导航 end*/

string dbName = DBNAME;
/**数据库连接库start*/
class SQLiteWrapper
{
public:
    SQLiteWrapper(const std::string &dbName);
    ~SQLiteWrapper();
    bool update(const std::string &sql);
    bool insert(const std::string &sql);
    bool deleteRow(const std::string &sql);
    std::vector<std::vector<std::string>> select(const std::string &sql);
    sqlite3 *db() { return db_; } // 添加获取db指针的方法

private:
    sqlite3 *db_;
    void checkError(int rc, const char *msg = nullptr);
    std::vector<std::string> getColumnNames(sqlite3_stmt *stmt);
    std::vector<std::vector<std::string>> getRows(sqlite3_stmt *stmt);
};
SQLiteWrapper::SQLiteWrapper(const std::string &dbName) : db_(nullptr)
{
    int rc = sqlite3_open(dbName.c_str(), &db_);
    if (rc)
    {
        throw std::runtime_error("Can't open database: " + std::string(sqlite3_errmsg(db_)));
    }
    // 启用WAL模式和设置超时
    sqlite3_exec(db_, "PRAGMA journal_mode=WAL; PRAGMA synchronous=NORMAL;", nullptr, nullptr, nullptr);
    sqlite3_busy_timeout(db_, 5000); // 5秒超时
}
SQLiteWrapper::~SQLiteWrapper()
{
    if (db_)
    {
        sqlite3_close(db_);
    }
}
void SQLiteWrapper::checkError(int rc, const char *msg)
{
    if (rc != SQLITE_OK)
    {
        if (msg)
        {
            throw std::runtime_error(std::string(msg) + ": " + std::string(sqlite3_errmsg(db_)));
        }
        else
        {
            throw std::runtime_error(std::string(sqlite3_errmsg(db_)));
        }
    }
}
bool SQLiteWrapper::update(const std::string &sql)
{
    char *errMsg = nullptr;
    int rc = sqlite3_exec(db_, sql.c_str(), nullptr, nullptr, &errMsg);
    if (rc != SQLITE_OK)
    {
        std::cerr << "SQL error: " << errMsg << std::endl;
        sqlite3_free(errMsg);
        return false;
    }
    return true;
}
bool SQLiteWrapper::insert(const std::string &sql)
{
    // 对于insert操作，我们可以重用update方法，因为它们在SQLite中通常没有返回结果集
    return update(sql);
}
bool SQLiteWrapper::deleteRow(const std::string &sql)
{
    char *errMsg = nullptr;
    int rc = sqlite3_exec(db_, sql.c_str(), nullptr, nullptr, &errMsg);
    if (rc != SQLITE_OK)
    {
        std::cerr << "SQL error: " << errMsg << std::endl;
        sqlite3_free(errMsg);
        return false;
    }
    // 如果需要，可以返回受影响的行数（但 sqlite3_exec 不直接提供这个信息）
    // 可以通过 sqlite3_changes() 获取，但通常不需要在这里立即返回
    return true;
}
std::vector<std::string> SQLiteWrapper::getColumnNames(sqlite3_stmt *stmt)
{
    std::vector<std::string> columnNames;
    int columnCount = sqlite3_column_count(stmt);
    for (int i = 0; i < columnCount; ++i)
    {
        const char *columnName = sqlite3_column_name(stmt, i);
        columnNames.push_back(std::string(columnName));
    }
    return columnNames;
}
std::vector<std::vector<std::string>> SQLiteWrapper::getRows(sqlite3_stmt *stmt)
{
    std::vector<std::vector<std::string>> rows;
    while (sqlite3_step(stmt) == SQLITE_ROW)
    {
        std::vector<std::string> row;
        int columnCount = sqlite3_column_count(stmt);
        for (int i = 0; i < columnCount; ++i)
        {
            const unsigned char *text = sqlite3_column_text(stmt, i);
            row.push_back(text ? reinterpret_cast<const char *>(text) : "");
        }
        rows.push_back(row);
    }
    return rows;
}
std::vector<std::vector<std::string>> SQLiteWrapper::select(const std::string &sql)
{
    sqlite3_stmt *stmt;
    const char *tail;
    int rc = sqlite3_prepare_v2(db_, sql.c_str(), -1, &stmt, &tail);
    if (rc != SQLITE_OK)
    {
        checkError(rc, "Failed to prepare statement");
        // 假设 checkError 在遇到错误时会抛出异常或处理资源清理
        sqlite3_finalize(stmt); // 如果 checkError 不负责，这里需要确保释放资源
        return {};              // 或者其他错误处理
    }
    std::vector<std::vector<std::string>> result;
    auto rows = getRows(stmt);
    result.insert(result.end(), rows.begin(), rows.end());

    sqlite3_finalize(stmt);
    return result;
}
/**数据库连接库end */

// 获取时间 年/月/日：小时/分钟/秒
std::string getCurrentTimeString()
{
    // 获取当前时间点
    auto now = std::chrono::system_clock::now();
    // 转换为 time_t 类型（C 风格时间）
    std::time_t now_time_t = std::chrono::system_clock::to_time_t(now);
    // 转换为 tm 结构（本地时间）
    std::tm *now_tm = std::localtime(&now_time_t);
    // 使用 ostringstream 来格式化时间字符串
    std::ostringstream oss;
    oss << std::put_time(now_tm, "%Y-%m-%d %H:%M:%S");
    return oss.str();
}
// 处理sql时间格式
std::string escapeString(const std::string &input)
{
    std::string output;
    output.reserve(input.size() + 2); // 预留空间，包括可能的转义字符和引号
    // 添加开头的单引号
    output += '\'';
    for (char c : input)
    {
        if (c == '\'')
        {
            // 转义单引号
            output += "''";
        }
        else
        {
            output += c;
        }
    }
    // 添加结尾的单引号
    output += '\'';
    return output;
}

// 四元数转航向角
double quaternionToEulerAngle(double ox, double oy, double oz, double ow)
{
    // 创建tf四元数
    tf::Quaternion quat(ox, oy, oz, ow);
    // 创建旋转矩阵
    tf::Matrix3x3 mat(quat); // 注意使用tf命名空间
    double roll, pitch, yaw;
    mat.getRPY(roll, pitch, yaw);
    // 转换为度数并调整范围
    double angle_deg = yaw * (180.0 / M_PI); // 弧度转度
    angle_deg = -angle_deg + 180.0;          // 调整方向
    if (angle_deg < 0)
        angle_deg += 360.0; // 确保在 [0, 360) 范围内

    return angle_deg;
}

// 接受网络传来指令存库
bool insert_cmd(const tcp_cmd &cmd)
{
    std::lock_guard<std::mutex> lock(g_db_mutex);
    SQLiteWrapper db(dbName);
    std::string insertSql = "INSERT INTO tcp_cmd (type,cmd,ctime) VALUES(" +
                            to_string(cmd.type) + ", " +
                            escapeString(cmd.cmd) + ", " +
                            escapeString(cmd.ctime) + ");";
    return db.insert(insertSql);
}

bool insert_fist_pose(const fist_pose &fist_pose_data)
{
    std::lock_guard<std::mutex> lock(g_db_mutex);
    SQLiteWrapper db(dbName);
    std::string insertSql = "INSERT INTO fist_pose (map_id) VALUES(" +
                            to_string(fist_pose_data.map_id) + ");";
    return db.insert(insertSql);
}

// 插入车辆日志
bool insert_car_log(const car_log &car_log_data)
{
    std::lock_guard<std::mutex> lock(g_db_mutex);
    SQLiteWrapper db(dbName);
    std::string insertSql = "INSERT INTO car_log (speed,x,y,z,lidar_heading,ndt_matching,rtk_x,rtk_y,rkt_z,lng,lat,rtk_heading,position_flag,orientation_flag,ctime) VALUES(" +
                            to_string(car_log_data.speed) + ", " +
                            to_string(car_log_data.x) + ", " +
                            to_string(car_log_data.y) + ", " +
                            to_string(car_log_data.z) + ", " +
                            to_string(car_log_data.lidar_heading) + ", " +
                            to_string(car_log_data.ndt_matching) + ", " +
                            to_string(car_log_data.rtk_x) + ", " +
                            to_string(car_log_data.rtk_y) + ", " +
                            to_string(car_log_data.rtk_z) + ", " +
                            to_string(car_log_data.lng) + ", " +
                            to_string(car_log_data.lat) + ", " +
                            to_string(car_log_data.rtk_heading) + ", " +
                            to_string(car_log_data.position_flag) + ", " +
                            to_string(car_log_data.orientation_flag) + ", " +
                            escapeString(car_log_data.ctime) + ");";
    // std::cout << insertSql << std::endl;
    return db.insert(insertSql);
}
// 插入PCB日志
bool insert_pcb_log(const pcb_log &pcb_log_data)
{
    std::lock_guard<std::mutex> lock(g_db_mutex);
    SQLiteWrapper db(dbName);
    std::string insertSql = "INSERT INTO pcb_log (model,soc,err,rpm_l,rpm_r,key_1,key_2,key_3,key_4,key_5,key_6,key_7,key_8,ladar_1,ladar_2,ladar_3,ladar_4,ladar_5,ladar_6,ladar_7,ladar_8,unknow_1,unknow_2,unknow_3,unknow_4,unknow_5,unknow_6,heartbeat,ctime) VALUES(" +
                            to_string(pcb_log_data.model) + ", " +
                            to_string(pcb_log_data.soc) + ", " +
                            to_string(pcb_log_data.err) + ", " +
                            to_string(pcb_log_data.rpm_l) + ", " +
                            to_string(pcb_log_data.rpm_r) + ", " +
                            to_string(pcb_log_data.key_1) + ", " +
                            to_string(pcb_log_data.key_2) + ", " +
                            to_string(pcb_log_data.key_3) + ", " +
                            to_string(pcb_log_data.key_4) + ", " +
                            to_string(pcb_log_data.key_5) + ", " +
                            to_string(pcb_log_data.key_6) + ", " +
                            to_string(pcb_log_data.key_7) + ", " +
                            to_string(pcb_log_data.key_8) + ", " +
                            to_string(pcb_log_data.ladar_1) + ", " +
                            to_string(pcb_log_data.ladar_2) + ", " +
                            to_string(pcb_log_data.ladar_3) + ", " +
                            to_string(pcb_log_data.ladar_4) + ", " +
                            to_string(pcb_log_data.ladar_5) + ", " +
                            to_string(pcb_log_data.ladar_6) + ", " +
                            to_string(pcb_log_data.ladar_7) + ", " +
                            to_string(pcb_log_data.ladar_8) + ", " +
                            to_string(pcb_log_data.unknow_1) + ", " +
                            to_string(pcb_log_data.unknow_2) + ", " +
                            to_string(pcb_log_data.unknow_3) + ", " +
                            to_string(pcb_log_data.unknow_4) + ", " +
                            to_string(pcb_log_data.unknow_5) + ", " +
                            to_string(pcb_log_data.unknow_6) + ", " +
                            to_string(pcb_log_data.heartbeat) + ", " +
                            escapeString(pcb_log_data.ctime) + ");";
    return db.insert(insertSql);
}
// 插入控制车日志
bool insert_driving_log(const car_driving_log &car_driving_log_data)
{
    std::lock_guard<std::mutex> lock(g_db_mutex);
    SQLiteWrapper db(dbName);
    std::string insertSql = "INSERT INTO driving_log (model, steering, acc, brake, gear, target_speed,rpml, rpmr, lignt, audio, sign,camera_hor, camera_ver, unknow_1, unknow_2,unknow_3, unknow_4, unknow_5, map_id, line_id,num_point, alltotal, ctime) VALUES(" +
                            to_string(car_driving_log_data.model) + ", " +
                            to_string(car_driving_log_data.steering) + ", " +
                            to_string(car_driving_log_data.acc) + ", " +
                            to_string(car_driving_log_data.brake) + ", " +
                            to_string(car_driving_log_data.gear) + ", " +
                            to_string(car_driving_log_data.target_speed) + ", " +
                            to_string(car_driving_log_data.rpml) + ", " +
                            to_string(car_driving_log_data.rpmr) + ", " +
                            to_string(car_driving_log_data.lignt) + ", " +
                            to_string(car_driving_log_data.audio) + ", " +
                            to_string(car_driving_log_data.sign) + ", " +
                            to_string(car_driving_log_data.camera_hor) + ", " +
                            to_string(car_driving_log_data.camera_ver) + ", " +
                            to_string(car_driving_log_data.unknow_1) + ", " +
                            to_string(car_driving_log_data.unknow_2) + ", " +
                            to_string(car_driving_log_data.unknow_3) + ", " +
                            to_string(car_driving_log_data.unknow_4) + ", " +
                            to_string(car_driving_log_data.unknow_5) + ", " +
                            to_string(car_driving_log_data.map_id) + ", " +
                            to_string(car_driving_log_data.line_id) + ", " +
                            to_string(car_driving_log_data.num_point) + ", " +
                            to_string(car_driving_log_data.alltotal) + ", " +
                            escapeString(car_driving_log_data.ctime) + ");";
    return db.insert(insertSql);
}
// 插入开机点
bool insert_start_machine(const start_machine &start_machine_data)
{
    std::lock_guard<std::mutex> lock(g_db_mutex);
    SQLiteWrapper db(dbName);
    std::string insertSql = "INSERT INTO start_machine (num, map_id, x, y, z, ox, oy, oz, ow,lidar_heading, ndt_matching, rtk_x, rtk_y, rtk_z, lng, lat,rtk_heading, position_flag, orientation_flag) VALUES(" +
                            to_string(start_machine_data.num) + ", " +
                            to_string(start_machine_data.map_id) + ", " +
                            to_string(start_machine_data.x) + ", " +
                            to_string(start_machine_data.y) + ", " +
                            to_string(start_machine_data.z) + ", " +
                            to_string(start_machine_data.ox) + ", " +
                            to_string(start_machine_data.oy) + ", " +
                            to_string(start_machine_data.oz) + ", " +
                            to_string(start_machine_data.ow) + ", " +
                            to_string(start_machine_data.lidar_heading) + ", " +
                            to_string(start_machine_data.ndt_matching) + ", " +
                            to_string(start_machine_data.rtk_x) + ", " +
                            to_string(start_machine_data.rtk_y) + ", " +
                            to_string(start_machine_data.rtk_z) + ", " +
                            to_string(start_machine_data.lng) + ", " +
                            to_string(start_machine_data.lat) + ", " +
                            to_string(start_machine_data.rtk_heading) + ", " +
                            to_string(start_machine_data.position_flag) + ", " +
                            to_string(start_machine_data.orientation_flag) + ");";
    return db.insert(insertSql);
}
// 删除开机点
bool delete_start_machine(const start_machine &start_machine_data)
{
    std::lock_guard<std::mutex> lock(g_db_mutex);
    SQLiteWrapper db(dbName);
    std::string deleteSql = "DELETE FROM start_machine WHERE map_id = " + std::to_string(start_machine_data.map_id) + " AND num=" + std::to_string(start_machine_data.num) + ";";
    return db.deleteRow(deleteSql);
}
// 创建带图带线路线点
bool insert_car(const car &car_data)
{
    std::lock_guard<std::mutex> lock(g_db_mutex);
    SQLiteWrapper db(dbName);
    std::string insertSql = "INSERT INTO car (x, y, z, lidar_heading, ndt_matching,rtk_x, rtk_y, rtk_z, rtk_heading, lng, lat,position_flag, orientation_flag, num_point, stop_flag, stop_time, line_id, map_id) VALUES(" +
                            to_string(car_data.x) + ", " +
                            to_string(car_data.y) + ", " +
                            to_string(car_data.z) + ", " +
                            to_string(car_data.lidar_heading) + ", " +
                            to_string(car_data.ndt_matching) + ", " +
                            to_string(car_data.rtk_x) + ", " +
                            to_string(car_data.rtk_y) + ", " +
                            to_string(car_data.rtk_z) + ", " +
                            to_string(car_data.rtk_heading) + ", " +
                            to_string(car_data.lng) + ", " +
                            to_string(car_data.lat) + ", " +
                            to_string(car_data.position_flag) + ", " +
                            to_string(car_data.orientation_flag) + ", " +
                            to_string(car_data.num_point) + ", " +
                            to_string(car_data.stop_flag) + ", " +
                            to_string(car_data.stop_time) + ", " +
                            to_string(car_data.line_id) + ", " +
                            to_string(car_data.map_id) + ");";
    return db.insert(insertSql);
}

// 马踏飞燕
bool insert_point_all(const point_all &point_all_data)
{
    std::lock_guard<std::mutex> lock(g_db_mutex);
    SQLiteWrapper db(dbName);
    std::string insertSql = "INSERT INTO point_all (map_id,x, y, z, lidar_heading, ndt_matching,rtk_x, rtk_y, rtk_z, lng, lat, rtk_heading,position_flag, orientation_flag) VALUES(" +
                            to_string(point_all_data.map_id) + ", " +
                            to_string(point_all_data.x) + ", " +
                            to_string(point_all_data.y) + ", " +
                            to_string(point_all_data.z) + ", " +
                            to_string(point_all_data.lidar_heading) + ", " +
                            to_string(point_all_data.ndt_matching) + ", " +
                            to_string(point_all_data.rtk_x) + ", " +
                            to_string(point_all_data.rtk_y) + ", " +
                            to_string(point_all_data.rtk_z) + ", " +
                            to_string(point_all_data.lng) + ", " +
                            to_string(point_all_data.lat) + ", " +
                            to_string(point_all_data.rtk_heading) + ", " +
                            to_string(point_all_data.position_flag) + ", " +
                            to_string(point_all_data.orientation_flag) + ");";
    return db.insert(insertSql);
}

// 任意点存库
bool insert_point_data(const point_data &point_data_data)
{
    std::lock_guard<std::mutex> lock(g_db_mutex);
    SQLiteWrapper db(dbName);
    std::string insertSql = "INSERT INTO point_data (x, y, z, lidar_heading, ndt_matching,rtk_x, rtk_y, rtk_z, lng, lat, rtk_heading,position_flag, orientation_flag,num_point,alltotal) VALUES(" +
                            to_string(point_data_data.x) + ", " +
                            to_string(point_data_data.y) + ", " +
                            to_string(point_data_data.z) + ", " +
                            to_string(point_data_data.lidar_heading) + ", " +
                            to_string(point_data_data.ndt_matching) + ", " +
                            to_string(point_data_data.rtk_x) + ", " +
                            to_string(point_data_data.rtk_y) + ", " +
                            to_string(point_data_data.rtk_z) + ", " +
                            to_string(point_data_data.lng) + ", " +
                            to_string(point_data_data.lat) + ", " +
                            to_string(point_data_data.rtk_heading) + ", " +
                            to_string(point_data_data.position_flag) + ", " +
                            to_string(point_data_data.orientation_flag) + ", " +
                            to_string(point_data_data.num_point) + ", " +
                            to_string(point_data_data.alltotal) + ");";
    return db.insert(insertSql);
}
// goback踩点
bool insert_go_back(const go_back &go_back_data)
{
    std::lock_guard<std::mutex> lock(g_db_mutex);
    SQLiteWrapper db(dbName);
    std::string insertSql = "INSERT INTO go_back (num_point, x, y, z, lidar_heading,rtk_x, rtk_y, rtk_z, rtk_heading,lng, lat, position_flag, orientation_flag) VALUES(" +
                            to_string(go_back_data.num_point) + ", " +
                            to_string(go_back_data.x) + ", " +
                            to_string(go_back_data.y) + ", " +
                            to_string(go_back_data.z) + ", " +
                            to_string(go_back_data.lidar_heading) + ", " +
                            to_string(go_back_data.rtk_x) + ", " +
                            to_string(go_back_data.rtk_y) + ", " +
                            to_string(go_back_data.rtk_z) + ", " +
                            to_string(go_back_data.rtk_heading) + ", " +
                            to_string(go_back_data.lng) + ", " +
                            to_string(go_back_data.lat) + ", " +
                            to_string(go_back_data.position_flag) + ", " +
                            to_string(go_back_data.orientation_flag) + ");";
    return db.insert(insertSql);
}
// 清空表go_back
bool delete_go_back()
{
    std::lock_guard<std::mutex> lock(g_db_mutex);
    SQLiteWrapper db(dbName);
    std::string deleteSql = "DELETE FROM go_back;";
    return db.insert(deleteSql);
}
// 清空表point_data
bool delete_point_data()
{
    std::lock_guard<std::mutex> lock(g_db_mutex);
    SQLiteWrapper db(dbName);
    std::string deleteSql = "DELETE FROM point_data;";
    return db.insert(deleteSql);
}

// 获取调度路线
std::vector<car> fetchCarDataFromDB(int map_id, int path_id)
{
    std::lock_guard<std::mutex> lock(g_db_mutex);
    SQLiteWrapper db(dbName);
    // 查询该路径上的所有点位
    std::string sql =
        "SELECT x, y, z, lidar_heading, ndt_matching, rtk_x, rtk_y, rtk_z, rtk_heading, "
        "lng, lat, position_flag, orientation_flag, num_point, stop_flag, stop_time, line_id, map_id "
        "FROM car "
        "WHERE map_id = " +
        std::to_string(map_id) +
        " AND line_id = " + std::to_string(path_id) +
        " ORDER BY num_point";

    auto result = db.select(sql);

    if (result.empty())
    {
        throw std::runtime_error("No data found for map_id=" + std::to_string(map_id) +
                                 ", path_id=" + std::to_string(path_id));
    }

    std::vector<car> carDataList;
    for (const auto &row : result)
    {
        car data;
        data.x = std::stod(row[0]);
        data.y = std::stod(row[1]);
        data.z = std::stod(row[2]);
        data.lidar_heading = std::stod(row[3]);
        data.ndt_matching = std::stod(row[4]);
        data.rtk_x = std::stod(row[5]);
        data.rtk_y = std::stod(row[6]);
        data.rtk_z = std::stod(row[7]);
        data.rtk_heading = std::stod(row[8]);
        data.lng = std::stod(row[9]);
        data.lat = std::stod(row[10]);
        data.position_flag = std::stoi(row[11]);
        data.orientation_flag = std::stoi(row[12]);
        data.num_point = std::stoi(row[13]);
        data.stop_flag = std::stoi(row[14]);
        data.stop_time = std::stoi(row[15]);
        data.line_id = std::stoi(row[16]);
        data.map_id = std::stoi(row[17]);
        carDataList.push_back(data);
    }

    return carDataList;
}

// 获取记忆路线
std::vector<go_back> fetchgobackDataFromDB()
{
    std::lock_guard<std::mutex> lock(g_db_mutex);
    SQLiteWrapper db(dbName);
    // 查询该路径上的所有点位
    std::string sql =
        "SELECT x, y, z, lidar_heading, rtk_x, rtk_y, rtk_z, rtk_heading, "
        "lng, lat, position_flag, orientation_flag, num_point "
        "FROM go_back ORDER BY num_point DESC";
    auto result = db.select(sql);

    if (result.empty())
    {
        throw std::runtime_error("No data found");
    }

    std::vector<go_back> carDataList;
    carDataList.reserve(result.size()); // 预分配内存

    try
    {
        for (const auto &row : result)
        {
            if (row.size() < 13)
            { // 检查每行数据完整性
                throw std::runtime_error("数据库行数据不完整，预期12列");
            }

            go_back data;
            data.x = std::stod(row[0]);
            data.y = std::stod(row[1]);
            data.z = std::stod(row[2]);
            data.lidar_heading = std::stod(row[3]);
            data.rtk_x = std::stod(row[4]); // 注意：row[4]似乎被跳过了？
            data.rtk_y = std::stod(row[5]);
            data.rtk_z = std::stod(row[6]);
            data.rtk_heading = std::stod(row[7]);
            data.lng = std::stod(row[8]);
            data.lat = std::stod(row[9]);
            data.position_flag = std::stoi(row[10]);
            data.orientation_flag = std::stoi(row[11]);
            data.num_point = std::stoi(row[12]);
            carDataList.push_back(data);
        }
    }
    catch (const std::exception &e)
    {
        throw std::runtime_error(std::string("获取数据失败: ") + e.what());
    }

    return carDataList;
}

// 获取地图编号
int getmapid()
{
    std::lock_guard<std::mutex> lock(g_db_mutex);
    SQLiteWrapper db(dbName);
    std::string sql = "SELECT MAX(map_id) FROM fist_pose;";
    auto result = db.select(sql);
    if (!result.empty() && !result[0].empty())
    {
        // 第一行第一列就是 MAX(map_id) 的值
        std::string max_id_str = result[0][0];

        // 如果表为空，max_id_str 可能是空字符串，需要处理
        if (max_id_str.empty())
        {
            return 0; // 或者返回 -1 表示没有记录
        }

        // 转换为整数并返回
        return std::stoi(max_id_str);
    }
    return 0; // 默认返回 0 或 -1 表示失败
}

// 获取任意点调度路线点
std::vector<car> fetchPointdataDataFromDB()
{
    std::lock_guard<std::mutex> lock(g_db_mutex);
    SQLiteWrapper db(dbName);
    // 查询该路径上的所有点位
    std::string sql =
        "SELECT x, y, z, lidar_heading, ndt_matching, rtk_x, rtk_y, rtk_z, rtk_heading, "
        "lng, lat, position_flag, orientation_flag,num_point"
        "FROM point_data "
        " ORDER BY num_point";

    auto result = db.select(sql);

    if (result.empty())
    {
        throw std::runtime_error("No data found");
    }

    std::vector<car> carDataList;
    for (const auto &row : result)
    {
        car data;
        data.x = std::stod(row[0]);
        data.y = std::stod(row[1]);
        data.z = std::stod(row[2]);
        data.lidar_heading = std::stod(row[3]);
        data.ndt_matching = std::stod(row[4]);
        data.rtk_x = std::stod(row[5]);
        data.rtk_y = std::stod(row[6]);
        data.rtk_z = std::stod(row[7]);
        data.rtk_heading = std::stod(row[8]);
        data.lng = std::stod(row[9]);
        data.lat = std::stod(row[10]);
        data.position_flag = std::stoi(row[11]);
        data.orientation_flag = std::stoi(row[12]);
        data.num_point = std::stoi(row[13]);
        data.stop_flag = 0;
        data.stop_time = 0;
        data.line_id = 0;
        data.map_id = 0;
        carDataList.push_back(data);
    }

    return carDataList;
}

// 获取所有基本点
std::vector<point_all> fetchPointallDataFromDB(int map_id)
{
    std::lock_guard<std::mutex> lock(g_db_mutex);
    SQLiteWrapper db(dbName);
    // 查询该路径上的所有点位
    std::string sql =
        "SELECT x, y, z, lidar_heading, ndt_matching, rtk_x, rtk_y, rtk_z, rtk_heading, "
        "lng, lat, position_flag, orientation_flag, map_id,num "
        "FROM point_all "
        "WHERE map_id = " +
        std::to_string(map_id) +
        " ORDER BY num";

    auto result = db.select(sql);

    if (result.empty())
    {
        throw std::runtime_error("No data found for map_id=" + std::to_string(map_id));
    }

    std::vector<point_all> ponit_allDataList;
    for (const auto &row : result)
    {
        point_all data;
        data.x = std::stod(row[0]);
        data.y = std::stod(row[1]);
        data.z = std::stod(row[2]);
        data.lidar_heading = std::stod(row[3]);
        data.ndt_matching = std::stod(row[4]);
        data.rtk_x = std::stod(row[5]);
        data.rtk_y = std::stod(row[6]);
        data.rtk_z = std::stod(row[7]);
        data.rtk_heading = std::stod(row[8]);
        data.lng = std::stod(row[9]);
        data.lat = std::stod(row[10]);
        data.position_flag = std::stoi(row[11]);
        data.orientation_flag = std::stoi(row[12]);
        data.map_id = std::stoi(row[13]);
        data.num = std::stoi(row[14]);
        ponit_allDataList.push_back(data);
    }

    return ponit_allDataList;
}
#endif
